FULLTEXTTABLE
FUNCTION
GENERAL
GET
GLOBAL
GO
GOTO
GRANT
GROUP
GROUPING
HAVING
HOLDLOCK
HOST
HOUR
HOUR
IDENTITY
IDENTITY_INSERT
IDENTITYCOL
IF
IGNORE
IMMEDIATE
IN
INCLUDE
INDEX
INDICATOR
INITIALIZE
INITIALLY
INNER
INOUT
INPUT
INSENSITIVE
INSERT
INT
INTEGER
INTERSECT
INTERVAL
INTO
IS
ISOLATION
ITERATE
JOIN
KEY
KILL
LANGUAGE
LARGE
LAST
LATERAL
LEADING
LEFT
LESS
LEVEL
LIKE
LIMIT
LINENO
LOAD
LOCAL
LOCALTIME
LOCALTIMESTAMP
LOCATOR
LOWER
MAP
MATCH
MAX
MIN
MINUTE
MODIFIES
MODIFY
MODULE
MONTH
NAMES
NATIONAL
NATURAL
NCHAR
NCLOB
NEW
NEXT
NO
NOCHECK
NONCLUSTERED
NONE
NOT
NULL
NULLIF
NUMERIC
OBJECT
OCTET_LENGTH
OF
OFF
OFFSETS
OLD
ON
ONLY
OPEN
OPENDATASOURCE
OPENQUERY
OPENROWSET
OPENXML
OPERATION
OPTION
OR
ORDER
ORDINALITY
OUT
OUTER
OUTPUT
OVER
OVERLAPS
PAD
PARAMETER
PARAMETERS
PARTIAL
PASCAL
PATH
PERCENT
PLAN
POSITION
POSTFIX
PRECISION
PREFIX
PREORDER
PREPARE
PRESERVE
PRIMARY
PRINT
PRIOR
PRIVILEGES
PROC
PROCEDURE
PUBLIC
RAISERROR
READ
READS
READTEXT
REAL
RECONFIGURE
RECURSIVE
REF
REFERENCES
REFERENCING
RELATIVE
REPLICATION
RESTORE
RESTRICT
RESULT
RETURN
RETURNS
REVOKE
RIGHT
ROLE
ROLLBACK
ROLLUP
ROUTINE
ROW
ROWCOUNT
ROWGUIDCOL
ROWS
RULE
SAVE
SAVEPOINT
SCHEMA
SCOPE
SCROLL
SEARCH
SECOND
SECTION
SELECT
SEQUENCE
SESSION
SESSION_USER
SET
SETS
SETUSER
SHUTDOWN
SIZE
SMALLINT
SOME
SPACE
SPECIFIC
SPECIFICTYPE
SQL
SQLCA
SQLCODE
SQLERROR
SQLEXCEPTION
SQLSTATE
SQLWARNING
START
STATE
STATEMENT
STATIC
STATISTICS
STRUCTURE
SUBSTRING
SUM
SYSTEM_USER
TABLE
TEMPORARY
TERMINATE
TEXTSIZE
THAN
THEN
TIME
TIMESTAMP
TIMEZONE_HOUR
TIMEZONE_MINUTE
TO
TOP
TRAILING
TRAN
TRANSACTION
TRANSLATE
TRANSLATION
TREAT
TRIGGER
TRIM
TRUE
TRUNCATE
TSEQUAL
UNDER
UNION
UNIQUE
UNKNOWN
UNNEST
UPDATE
UPDATETEXT
UPPER
USAGE
USE
USER
USING
VALUE
VALUES
VARCHAR
VARIABLE
VARYING
VIEW
WAITFOR
WHEN
WHENEVER
WHERE
WHILE
WITH
WITHOUT
WORK
WRITE
WRITETEXT
YEAR
ZONE
Index
Symbols
* (asterisk)
multiplication operator, 80
wildcard character, 35–36, 101
@ (at symbol), 214, 233
[ ] (brackets), 69–70, 275
/ (division) operator, 80
= (equality) operator, 51
> (greater than) operator, 51
>= (greater than or equal to) operator, 51
< (less than) operator, 51, 52
<= (less than or equal to) operator, 51
<> (nonequality) operator, 51, 53
!= (nonequality) operator, 51, 53
!> (not greater than) operator, 51
!< (not less than) operator, 51
( ) (parentheses), 61
% (percent sign)
modulo operator, 80
wildcard character, 66–68
+ (plus sign)
addition operator, 80
concatenation operator, 74–77
; (semicolon), 33, 179–180
’ (single quotes), 53, 169, 220
- (subtraction) operator, 80
_ (underscore), 68–69
A
Abs() function, 94
ABSOLUTE keyword, 243
absolute value, returning, 94
access control
access rights, 291–293
explained, 287–288
logins, renaming, 291
user accounts
creating, 290
deleting, 290
enabling/disabling, 290
user management, 289
access rights, 291–293
accessing SQL Server, 16–17
accounts (user)
creating, 290
deleting, 290
enabling/disabling, 290
addition operator (+), 80
administrative login, 19
AFTER triggers, 249
aggregate functions
Avg(), 98–99
combining, 106–107
Count(), 100–101, 106
distinct values, 105
explained, 97–98
with joins, 148–150
Max(), 101–102, 106
Min(), 102–103
Sum(), 103–104
aliases
calculated field aliases, 77–78
naming, 107
table aliases, 141–142
all columns, retrieving, 35–36
ALL keyword, 105
ALTER FULLTEXT statement, 165–166
ALTER LOGIN statement, 290–291
ALTER TABLE statement, 199–200, 280, 291
ALTER TRIGGER statement, 249
altering tables, 199–200
AND operator, 57–58, 170
application level, filtering data at, 50
ASC keyword, 46–47
ascending order, sorting data in, 44–46
assigning
search term weights, 173
values to variables, 215–216
asterisk (*)
multiplication operator, 80
wildcard character, 35–36, 101
AUTO keyword, 266
autocommits, 259, 261
averages, returning, 98–99
Avg() function, 98–99
B
batch statements, 219
BEGIN statement, 223–224
BETWEEN operator, 51, 54
Boolean operators. See operators
brackets ([ ]), 69–70, 275
BREAK statement, 226
C
calculated fields
aliases, 77–78
concatenating, 74–77
explained, 73–74
mathematical calculations, 79–80
subqueries as, 123–126
views, 210–211
Cartesian products, 133–137
case sensitivity
collation sequences, 281–283
data sorting, 46
searches, 67
statements, 33
Cast() function, 270
catalogs, full-text
creating, 163
dropping, 165–166
managing, 165–166
character sets
definition of, 277
Unicode, 283–285
CharIndex() function, 85
checking
for existance, 126–128
for nonmatches, 53
for NULL values, 54–55
for range of values, 54
against single values, 51–52
FROM clause, 133
clauses. See also keywords; statements
ELSE, 222
FOR JSON AUTO, 273
FOR XML, 264–268
FROM, 133
FULL OUTER JOIN, 148
GROUP BY, 110–111, 115–116
HAVING, 111–114
INNER JOIN, 137–138
JSON PATH, 274
ORDER BY
example, 115–116
sorting by multiple columns, 43–44
sorting by single column, 41–43
order of, 117
OUTER JOIN, 146–147
OUTPUT
DELETE statement, 188–189
INSERT statement, 183–184
UPDATE statement, 188–189
WHERE
AND operator, 57–58
brackets ([ ]) wildcard, 69–70
comparison operators. See comparison operators
joins, 133–137
LIKE operator, 65–66
nonmatches, checking for, 53
NULL values, checking for, 54–55
percent sign (%) wildcard, 66–68
range of values, checking for, 54
single values, checking against, 51–52
subqueries, 119–128
syntax, 49–50
underscore (_) wildcard, 68–69
views, 209
client server software, 14–15
client utilities, 15–16
CLOSE statement, 241–242
closing cursors, 241–242
COLLATE keyword, 281–283
collations
case sensitivity, 281–283
collation sequences, 278–280
definition of, 277
importance of, 278
columns. See also fields
aliases, 77–78
average value of, 98–99
deleting
deleted data, reporting on, 188–189
guidelines, 188–189
syntax, 187–188
explained, 7–8
fully qualified column names, 133
identity columns, 28
maximum value, 101–102
minimum value, 102–103
omitting from INSERT operations, 179
returning list of, 27–28
sorting by
multiple columns, 43–44
single columns, 41–43
totals, 103–104
updating
guidelines, 188–189
syntax, 185–187
updated data, reporting on, 188–189
combined queries
creating, 154–157
duplicate rows, including/eliminating, 157–158
explained, 153
sorting results of, 158–159
combining aggregate functions, 106–107
comments, 220
COMMIT statement, 259
commits
autocommits, 259, 261
explicit commits, 259
comparison operators
BETWEEN operator, 54
equality (=) operator, 51
IS NULL operator, 54–55
less than (<) operator, 52
nonequality (<>) operator, 53
nonequality (!=) operator, 53
table of, 49–50
complete rows, inserting, 176–179
concatenating fields, 74–77
concatenation (+) operator, 74–77
conditional processing
BEGIN statement, 223–224
END statement, 223–224
IF statement, 221–223
WHILE statement, 225–226
configuring full-text searching
full-text catalogs, 163–166
full-text indexes, 164–166
full-text search support, 162–163
connecting to SQL Server, 19–20
CONTAINS keyword, 168–172
CONTAINSTABLE() function, 172–173
contents of variables, viewing, 216–217
CONTINUE statement, 226
controlling access. See access control
converting
numeric values to strings, 85
strings to lowercase, 85
strings to uppercase, 85
variables to strings, 217
correlated subqueries, 125–126
Cos() function, 94
cosine, returning, 94
Count() function
DISTINCT keyword, 100–101
explained, 100–101
as subqueries, 124–125
counting rows, 100–101
CREATE FULLTEXT CATALOG statement, 163
CREATE FULLTEXT INDEX statement, 164
CREATE LOGIN statement, 290
CREATE PROCEDURE statement, 230–231
CREATE TABLE statement
collation sequences, 280
DEFAULT keyword, 197–198
default values, 197–198
example, 269
IDENTITY keyword, 195–197
NULL values, 193–194
primary keys, 194–195
syntax, 192–193
CREATE TRIGGER statement, 248
CREATE VIEW statement, 205
create.sql file, 22
creating
calculated fields
aliases, 77–78
concatenating, 74–77
explained, 73–74
mathematical calculations, 79–80
combined queries, 154–157
cursors, 240–241
full-text catalogs, 163
full-text indexes, 164–165
groups, 110–111
joins, 132–133
sample tables, 22–23
stored procedures, 230–231
tables
basic table creation, 192–193
DEFAULT keyword, 197–198
default values, 197–198
explained, 191
IDENTITY keyword, 195–197
NULL values, 193–194
primary keys, 194–195
triggers, 248
user accounts, 290
views, 205
cross joins, 136
cursors
closing, 241–242
creating, 240–241
deleting, 240–241
explained, 239–240
fetched data, 242–245
opening, 241–242
with WHILE loops, 225
D
data
cursor data, fetching, 242–245
deleting
deleted data, reporting on, 188–189
guidelines, 188–189
syntax, 187–188
filtering
at application level, 50
comparison operators, 49–50
filter conditions, 51–55
groups, 111–114
IS NULL operator, 54–55
multiple WHERE clauses, 57–64
by subqueries, 119–123
views, 209
WHERE clause syntax, 49–50
wildcard filtering, 65–70
formatting
client versus server formatting, 74
overview, 35
queries, 122
retrieved data, 207–208
statements, 193
groups
creating, 110–111
explained, 109–110
filtering, 111–114
grouping versus sorting, 114–116
inserting
complete rows, 176–179
explained, 175
list of changes, returning, 183–184
multiple rows, 179–180
retrieved data, 180–183
JSON (JavaScript Object Notation) data
functions, 275
retrieving data as JSON, 273–274
retrieving
all columns, 35–36
distinct rows, 36–37
fully qualified table names, 39–40
individual columns, 31–33
multiple columns, 33–34
overview, 31
results, limiting, 37–39
sorting
ascending order, 46–47
case sensitivity, 46
descending order, 44–46
dictionary sort order, 46
grouping versus sorting, 114–116
by multiple columns, 43–44
by single column, 41–43
summarizing with aggregate functions
Avg(), 98–99
Count(), 100–101, 106
distinct values, 105
explained, 97–98
Max(), 101–102, 106
Min(), 101–102
multiple aggregate functions, 106–107
Sum(), 103–104
updating
guidelines, 188–189
syntax, 185–187
updated data, reporting on, 188–189
XML (Extensible Markup Language) data
retrieving, 264–268
searching for, 271–272
storing, 268–270
support for, 263
XML schemas, 271
Database Engine Tuning Advisor, 296–297
Database Management System (DBMS), 6. See also SQL Server
database servers, 14
DatabasePropertyEX() function, 279
databases. See also tables
definition of, 6
everyday uses of, 5
master databases, 25
returning list of, 26
scaling, 131
selecting, 24–25
SYS, 289
datatypes
date time, 91
explained, 7–8
NCHAR, 283
NTEXT, 283
NVARCHAR, 283
parameter datatypes, 233
date manipulation functions, 87–94
date time datatype, 91
DateAdd() function, 87
DateDiff() function, 87, 91–93
DateName() function, 87
DatePart() function, 87–90, 221
Day() function, 87, 92–93
DBMS (Database Management System), 6. See also SQL Server
DEALLOCATE statement, 240–241
debugger, 218
DECLARE statement, 214–215, 233, 240–241
declaring variables, 214–215
DEFAULT keyword, 197–198
default values
assigning to variables, 215
specifying, 197–198
DELETE statement
DELETE FROM, 188
DELETE triggers, 252
deleted data, reporting on, 188–189
guidelines, 188–189
syntax, 187–188
DELETE triggers, 252
deleted data, reporting on, 188–189
deleting
cursors, 240–241
data
deleted data, reporting on, 188–189
guidelines, 188–189
syntax, 187–188
tables, 201
user accounts, 290
derived columns (aliases), 78
DESC keyword, 44–46
descending order, sorting data in, 44–46
dialogs, New Database, 23
dictionary sort order, 46
different tables, combining, 159
DISABLE TRIGGER statement, 250
disabling
INSERT statement, 175
triggers, 249–250
user accounts, 290
DISTINCT keyword, 36–37, 105–106
distinct values, aggregates on, 105
division operator (/), 80
documentation, 297
downloading SQL Server, 17
DROP FULLTEXT statement, 165–166
DROP LOGIN statement, 290
DROP PROCEDURE statement, 231–232
DROP TABLE statement, 201
DROP TRIGGER statement, 249
DROP VIEW statement, 205
dropping
catalogs/indexes, 165–166
logins, 290
stored procedures, 231–232
tables, 201
triggers, 249
views, 205
duplicate rows in unions, including/eliminating, 157–158
E
ELEMENTS keyword, 266–267
eliminating duplicate rows in unions, 157–158
ELSE clause, 222
empty strings, 194
ENABLE TRIGGER statement, 250
enabling
full-text searching, 162–163
triggers, 249–250
user accounts, 290
encodings, 277
END statement, 223–224
equality (=) operator, 51
equijoins, 137
evaluation, order of, 59–61, 80
EXEC statement, 230
EXECUTE statement, 230
executing stored procedures, 230
exist function, 271
existance, checking for, 126–128
EXISTS keyword, 126–128
Exp() function, 94
explicit commits, 259
EXPLICIT keyword, 267–268
exponential values, returning, 94
F
FETCH statement, 242–245
@@FETCH_STATUS function, 244
fetching data, 242–245
fields. See also columns
calculated fields
aliases, 77–78
concatenating, 74–77
explained, 73–74
mathematical calculations, 79–80
subqueries as, 123–126
views, 210–211
identity fields, 176–177
files
create.sql, 22
populate.sql, 22
filter conditions
comparison operators, 49–50
multiple WHERE clauses
AND operator, 57–58
IN operator, 61–63
NOT operator, 63–64
OR operator, 58–59
order of evaluation, 59–61
nonmatches, 53
NULL values, 54–55
range of values, 54
single values, 51–52
WHERE clause, 49–50
wildcard filtering
brackets ([ ]) wildcard, 69–70
case sensitivity, 67
LIKE operator, 65–66
NULL values, 68
percent sign (%) wildcard, 66–68
tips for, 71
underscore (_) wildcard, 68–69
filtering data
at application level, 50
comparison operators, 49–50
groups, 111–114
IS NULL operator, 54–55
multiple WHERE clauses
NOT operator, 63–64
AND operator, 57–58
OR operator, 58–59
IN operator, 61–63
order of evaluation, 59–61
nonmatches, 53
NULL values, 54–55
range of values, 54
single value, 51–52
by subqueries, 119–123
views, 209
WHERE clause syntax, 49–50
wildcard filtering
brackets ([ ]) wildcard, 69–70
case sensitivity, 67
LIKE operator, 65–66
NULL values, 68
percent sign (%) wildcard, 66–68
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 68–69
FIRST keyword, 243
fn_helpcollations() function, 278, 279–280
foreign keys, 11, 130
formatting
client versus server formatting, 74
overview, 35
queries, 122
retrieved data, 207–208
statements, 193
FORMSOF() function, 172
FOR XML clause, 264–268
four-digit year format, 90
FREETEXT keyword, 166–168
FULL OUTER JOIN clause, 148
full-text catalogs
creating, 163
managing, 165–166
full-text indexes
creating, 164–165
managing, 165–166
full-text searching
enabling, 162–163
explained, 161–162
full-text catalogs
creating, 163
managing, 165–166
full-text indexes
creating, 164–165
managing, 165–166
performance, 161
performing with CONTAINS, 168–172
performing with FREETEXT, 166–168
search results, ranking, 172–173
FulltextCatalogProperty() function, 166
FULLTEXTTABLE() function, 172–173
fully qualified column names, 133
fully qualified table names, 39–40
functions
Abs(), 94
Avg(), 98–99
Cast(), 270
CharIndex(), 85
CONTAINSTABLE(), 172–173
Cos(), 94
Count()
DISTINCT keyword, 106
explained, 100–101
as subqueries, 124–125
DatabasePropertyEX(), 279
DateAdd(), 87
DateDiff(), 87, 91–93
DateName(), 87
DatePart(), 87–90, 221
Day(), 87, 92–93
exist, 271
Exp(), 94
explained, 83
@@FETCH_STATUS, 244
fn_helpcollations(), 278, 279–280
FORMSOF(), 172
FulltextCatalogProperty(), 166
FULLTEXTTABLE(), 172–173
GetDate(), 87, 198, 221
@@IDENTITY system function, 197
ISABOUT(), 173
ISJSON(), 275
JSON_MODIFY(), 275
JSON_QUERY(), 275
JSON_VALUE(), 275
Left(), 85
Len(), 85
Lower(), 85
LTrim(), 77, 85
Max()
DISTINCT keyword, 106
explained, 101–102
order numbers, determining, 196
Min(), 102–103
modify, 271
Month(), 87, 92–93
nodes, 271
Pi(), 94
portability, 83
query, 271
Rand(), 94
Replace(), 85
Right(), 85
Round(), 94
RTrim(), 76–77, 85
ServerProperty(), 279
Sin(), 94
Soundex(), 85–86
Sqrt(), 94
Square(), 94
Str(), 85
SubString(), 85
Sum(), 103–104
Tan(), 94
testing, 80
types of
aggregate functions, 97–107
date and time manipulation functions, 87–94
numeric manipulation functions, 94
overview, 84
text manipulation functions, 84–86
Upper(), 84–85
value, 271
Year(), 87, 92–93
G
GetDate() function, 198, 221
globalization
character sets
definition of, 277
Unicode, 283–285
collations
case sensitivity, 281–283
collation sequences, 278–280
definition of, 277
importance of, 278
encodings, 277
GRANT statement, 291–292
granting access rights, 291–292
great than (>) operator, 51
greater than or equal to (>=) operator, 51
GROUP BY clause (SELECT statement), 110–111, 115–116
grouping statements, 223–224
groups
creating, 110–111
explained, 109–110
filtering, 111–114
grouping versus sorting, 114–116
H
HAVING clause (SELECT statement), 111–114
hostname, 20
I
identity, 28
identity fields, 176–177
IDENTITY keyword, 195–197
@@IDENTITY system function, 197
IF statement, 221–223
IMPLICIT_TRANSACTIONS setting, 261
improving performance, 295–297
including duplicate rows in unions, 157–158
incrementally building queries, 128
indexes
full-text indexes
creating, 164–165
managing, 165–166
updating, 165
individual columns, retrieving, 31–33
INNER JOIN clause, 137–138
inner joins, 137
IN operator, 61–63
INSERT statement
disabling, 175
INSERT SELECT, 180–183
INSERT triggers, 251–252
inserting complete rows, 176–179
inserting multiple rows, 179–180
inserting retrieved data, 180–183
INTO keyword, 179
omitting columns from, 179
OUTPUT clause, 183–184
overview, 175
returning list of changes, 183–184
Unicode text, 284–285
VALUES keyword, 177–179
INSERT triggers, 251–252
inserting data
complete rows, 176–179
explained, 175
multiple rows, 179–180
retrieved data, 180–183
returning, 183–184
installing SQL Server, 18
INSTEAD OF triggers, 249
integrity, referential, 131
intelligent stored procedures, 235–238
IS NULL operator, 51, 54–55
ISABOUT() function, 173
ISJSON() function, 275
J
JavaScript Object Notation. See JSON (JavaScript Object Notation)
joins
advantages of, 131
with aggregate functions, 148–150
Cartesian products, 133–137
creating, 132–133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138–140
natural joins, 145
outer joins, 145–148
performance considerations, 139
referential integrity, 131
self joins, 142–144
simplifying with views, 206–207
WHERE clause, 133–137
JSON (JavaScript Object Notation)
functions, 275
retrieving data as JSON, 273–274
FOR JSON AUTO clause, 273
JSON PATH clause, 274
JSON_MODIFY() function, 275
JSON_QUERY() function, 275
JSON_VALUE() function, 275
K
keys
foreign keys, 11, 130
primary keys
creating, 194–195
definition of, 9–11, 130
and NULL values, 195
FROM keyword, 31–33
AS keyword, 78
IN keyword, 155
INTO keyword, 179
keywords. See also clauses; statements
ABSOLUTE, 243
ALL, 105
AS, 78
ASC, 46–47
AUTO, 266
COLLATE, 281–283
CONTAINS, 168–172
DEFAULT, 197–198
DESC, 44–46
DISTINCT, 36–37, 105–106
ELEMENTS, 266–267
EXISTS, 126–128
EXPLICIT, 267–268
FIRST, 243
FREETEXT, 166–168
FROM
individual columns, retrieving, 31–33
multiple columns, retrieving, 33–34
IDENTITY, 195–197
IN, 155
INTO, 179
LAST, 243
LEFT, 146–147
NEAR, 170–171
NEXT, 243
NULL
aggregate functions and, 100–103
checking for, 54–55
CREATE TABLE statement, 193–194
null value elimination warning, 150
UPDATE statement, 187
wildcards and, 68
OUTPUT
DELETE statement, 188–189
INSERT statement, 183–184
UPDATE statement, 188–189
PERCENT, 38–39
PRIOR, 243
RAW, 266–267
RIGHT, 146–147
TOP, 37–39
UNION, 154–157
VALUES, 177–179
L
LAST keyword, 243
Left() function, 85
LEFT keyword, 146–147
left outer joins, 147
Len() function, 85
less than (<) operator, 51, 52
less than or equal to (<=) operator, 51
LIKE operator, 65–66
limiting results, 37–39
listing
columns, 27–28
databases, 26
inserted data, 183–184
tables, 26–27
triggers, 250
local servers, 17
localization
character sets
definition of, 277
Unicode, 283–285
collations
case sensitivity, 281–283
collation sequences, 278–280
definition of, 277
importance of, 278
encodings, 277
locating SQL Server Management Studio, 18
logging in, 19
logins
dropping, 290
renaming, 291
sa (system administrator), 288
loops, WHILE, 225–226
Lower() function, 85
lowercase, converting strings to, 85
LTrim() function, 77, 85
M
maintaining referential integrity, 131
Management Studio. See SQL Server Management Studio
managing
full-text catalogs, 165–166
full-text indexes, 165–166
users, 289
master databases, 25
mathematical calculations, 79–80
mathematical operators, 80
Max() function
DISTINCT keyword, 106
explained, 101–102
order numbers, determining, 196
maximum value, returning, 101–102
Microsoft SQL Server Management Studio. See SQL Server Management Studio
Min() function, 102–103
minimum value, returning, 102–103
modify function, 271
Month() function, 87, 92–93
multiple columns
retrieving, 33–34
sorting by, 43–44
multiple rows, inserting, 179–180
multiple tables, joining, 138–140
multiple WHERE clauses
AND operator, 57–58
IN operator, 61–63
NOT operator, 63–64
OR operator, 58–59
order of evaluation, 59–61
multiplication (*) operator, 80
multistatement triggers, 252
N
N prefix, 285
names
alias names, 107
fully qualified column names, 133
savepoint names, 260
natural joins, 145
navigating SQL Server Management Studio, 20–22
NCHAR datatype, 283
NEAR operator, 170–171
New Database dialog, 23
New Query button, 20–21
NEXT keyword, 243
nodes function, 271
non-ANSI outer joins, 147–148
nonequality (<>) operator, 51, 53
nonequality (!=) operator, 51, 53
nonmatches, checking for, 53
nonnumeric data, aggregate functions and
Max(), 102
Min(), 103
not greater than (!>) operator, 51
not less than (!<) operator, 51
NOT operator, 63–64
NTEXT datatype, 283
null value elimination warning, 150
NULL values
aggregate functions and
Avg(), 100
Count(), 101
Max(), 102
Min(), 103
Sum(), 104
checking for, 54–55
compared to empty strings, 194
CREATE TABLE statement, 193–194
null value elimination warning, 150
primary keys and, 195
UPDATE statement, 187
wildcards and, 68
numeric manipulation functions, 94
numeric values, converting to strings, 85
NVARCHAR datatype, 283
O
Object Explorer, 21
obtaining SQL Server, 17
omitting columns from INSERT operations, 179
OPEN statement, 241–242
opening cursors, 241–242
operators. See also keywords
AND, 57–58, 170
comparison operators
BETWEEN operator, 54
equality operator (=), 51
IS NULL operator, 54–55
less than operator (<), 52
nonequality operator (<>), 53
nonequality operator (!=), 53
table of, 49–50
concatenation (+) operator, 74–77
CONTAINS, 168–172
FREETEXT, 166–168
IN, 61–63
LIKE, 65–66
mathematical operators, 80
NEAR, 170–171
NOT, 63–64
OR, 58–59, 222–223
order of evaluation, 59–61
optimizing performance, 295–297
order
of clauses, 117
of evaluation, 59–61, 80
ORDER BY clause
example, 115–116
sorting by multiple columns, 43–44
sorting by single column, 41–43
OR operator, 58–59, 222–223
OUTER JOIN clause, 146–147
outer joins, 145–148
OUTPUT clause
DELETE statement, 188–189
INSERT statement, 183–184
UPDATE statement, 188–189
overriding IDENTITY keyword, 196
P
parameters of stored procedures, 232–235
parentheses, 61
PERCENT keyword, 38–39
percent sign (%)
modulo operator, 80
wildcard character, 66–68
performance
full-text searching, 161
improving, 295–297
joins, 139
pi, returning, 94
Pi() function, 94
plus sign (+)
addition operator, 80
concatenation operator, 74–77
populate.sql file, 22
portability, 83
precedence, order of, 80
predicates. See also keywords; operators
AND, 170
CONTAINS, 168–172
definition of, 66
FREETEXT, 166–168
IN, 61–63
LIKE, 65–66
NEAR, 170–171
NOT, 63–64
OR, 58–59, 222–223
primary keys
creating, 194–195
definition of, 9–11, 130
and NULL values, 195
PRINT statement, 217
PRIOR keyword, 243
procedures. See stored procedures
processing
conditional processing
BEGIN statement, 223–224
END statement, 223–224
IF statement, 221–223
WHILE statement, 225–226
transaction processing
autocommits, 259, 261
COMMIT statement, 259
explained, 255–257
ROLLBACK statement, 258–259
savepoints, 260–261
programming
comments, 220
conditional processing
BEGIN statement, 223–224
END statement, 223–224
IF statement, 221–223
WHILE statement, 225–226
explained, 213
variables
@ (at symbol), 214
assigning values to, 215–216
converting to strings, 217
declaring, 214–215
definition of, 214
rules and restrictions, 214
in statements, 218–220
viewing contents of, 216–217
Properties panel, 21
Q
queries
aggregate functions
Avg(), 98–99
combining, 106–107
Count(), 100–101, 106
distinct values, 105
explained, 97–98
with joins, 148–150
Max(), 101–102, 106
Min(), 102–103
Sum(), 103–104
AS keyword, 78
building incrementally, 128
combined queries
creating, 154–157
duplicate rows, including/eliminating, 157–158
explained, 153
sorting results of, 158–159
concatenation (+) operator, 74–77
cursors
closing, 241–242
creating, 240–241
deleting, 240–241
explained, 239–240
fetched data, 242–245
opening, 241–242
formatting, 122
GROUP BY clause, 110–111, 115–116
HAVING clause, 111–114
joins
advantages of, 131
with aggregate functions, 148–150
Cartesian products, 133–137
creating, 132–133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138–140
natural joins, 145
outer joins, 145–148
performance considerations, 139
referential integrity, 131
self joins, 142–144
WHERE clause, 133–137
ORDER BY clause, 115–116
sequence of clauses, 117
subqueries
as calculated fields, 123–126
checking for existance with, 126–128
correlated subqueries, 125–126
definition of, 119
EXISTS keyword, 126–128
filtering by, 119–123
WHERE clause
IN operator, 61–63
NOT operator, 63–64
OR operator, 58–59
order of evaluation, 59–61
wildcard filtering
brackets ([ ]) wildcard, 69–70
case sensitivity, 67
LIKE operator, 65–66
NULL values, 68
percent sign (%) wildcard, 66–68
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 68–69
query function, 271
quotation marks, 220
quotes, single (’), 53, 169, 220
R
Rand() function, 94
range of values, checking for, 54
ranking search results, 172–173
RAW keyword, 266–267
referential integrity, maintaining, 131
reformatting retrieved data, 207–208
relational tables
aliases, 141–142
explained, 129–131
formatting, 130
joins
advantages of, 131
with aggregate functions, 148–150
Cartesian products, 133–137
creating, 132–133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138–140
natural joins, 145
outer joins, 145–148
performance considerations, 139
referential integrity, 131
self joins, 142–144
WHERE clause, 133–137
primary keys, 130
removing
access rights, 292–293
views, 205
renaming
logins, 291
tables, 201
Replace() function, 85
reporting
on deleted data, 188–189
on inserted data, 183–184
on updated data, 188–189
restrictions (views), 205
results, limiting, 37–39
Results panel, 21
retrieved data
inserting, 180–183
reformatting, 207–208
retrieving data
columns
all columns, 35–36
individual columns, 31–33
multiple columns, 33–34
JSON (JavaScript Object Notation) data, 273–274
overview, 31
rows, 36–37
XML (Extensible Markup Language) data, 264–268
reusable views, 207
REVOKE statement, 292–293
revoking access rights, 292–293
Right() function, 85
RIGHT keyword, 146–147
right outer joins, 147
ROLLBACK statement, 258–259
rolling back statements, 258–259
Round() function, 94
rounding numbers, 94
rows
counting, 100–101
deleting
deleted data, reporting on, 188–189
guidelines, 188–189
syntax, 187–188
duplicate rows in unions, including/eliminating, 157–158
explained, 9–10
inserting
complete rows, 176–179
list of changes, returning, 183–184
multiple rows, 179–180
retrieved data, 180–183
retrieving, 36–37
updating
guidelines, 188–189
syntax, 185–187
updated data, reporting on, 188–189
RTrim() function, 76–77, 85
rules (views), 205
running stored procedures, 230
S
sa (system administrator) login, 288
SAVE TRANSACTION statement, 260–261
savepoints, 260–261
scaling databases, 131
schemas (XML), 271
search criteria
comparison operators, 49–50
multiple WHERE clauses
AND operator, 57–58
IN operator, 61–63
NOT operator, 63–64
OR operator, 58–59
order of evaluation, 59–61
nonmatches, 53
NULL values, 54–55
range of values, 54
single values, 51–52
WHERE clause, 49–50
wildcard filtering
brackets ([ ]) wildcard, 69–70
case sensitivity, 67
LIKE operator, 65–66
NULL values, 68
percent sign (%) wildcard, 66–68
tips for, 71
underscore (_) wildcard, 68–69
search results, ranking, 172–173
search term weights, assigning, 173
searching
full-text searching
enabling, 162–163
explained, 161–162
full-text catalogs, 163–166
full-text indexes, 164–166
performance, 161
performing with CONTAINS, 168–172
performing with FREETEXT, 166–168
search results, ranking, 172–173
search term weights, assigning, 173
SELECT
aggregate functions, 97–107
ASC keyword, 46–47
AS keyword, 78
asterisk (*) wildcard, 35–36
clause ordering, 117
comparison operators, 49–50
concatenation (+) operator, 74–77
DESC keyword, 44–46
DISTINCT keyword, 36–37
EXISTS keyword, 126–128
formatting, 122
FROM keyword, 31–34
fully qualified table names, 39–40
GROUP BY clause, 110–111, 115–116
HAVING clause, 111–114
LIKE operator, 65–66
multiple columns, retrieving, 33–34
ORDER BY clause, 41–44, 115–116
overview, 31
PERCENT keyword, 38–39
TOP keyword, 37–39
WHERE clause. See WHERE clause
wildcard filtering, 65–71
subqueries
as calculated fields, 123–126
checking for existance with, 126–128
correlated subqueries, 125–126
definition of, 119
filtering by, 119–123
wildcard filtering
* (asterisk), 61–62
brackets ([ ]) wildcard, 69–70
case sensitivity, 67
LIKE operator, 65–66
percent sign (%) wildcard, 66–68
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 68–69
for XML (Extensible Markup Language) data, 271–272
security
access rights, 291–293
explained, 287–288
logins, renaming, 291
user accounts
creating, 290
deleting, 290
enabling/disabling, 290
user management, 289
SELECT statement
aggregate functions
Avg(), 98–99
combining, 106–107
Count(), 100–101, 106
distinct values, 105
explained, 97–98
with joins, 148–150
Max(), 101–102, 106
Min(), 102–103
Sum(), 103–104
ASC keyword, 44–46
AS keyword, 78
asterisk (*) wildcard, 35–36
batch processing, 218–219
COLLATE keyword, 281–283
combined queries
creating, 154–157
duplicate rows, including/eliminating, 157–158
explained, 153
sorting results of, 158–159
comparison operators, 49–50
concatenation (+) operator, 74–77
DESC keyword, 44–46
DISTINCT keyword, 36–37
EXISTS keyword, 126–128
FOR JSON AUTO clause, 273
formatting, 122
FOR XML clause, 264–268
FROM clause, 133
FULL OUTER JOIN clause, 148
fully qualified table names, 39–40
GROUP BY clause, 110–111, 115–116
HAVING clause, 111–114
INNER JOIN clause, 137–138
INSERT SELECT, 180–183
joins
advantages of, 131
with aggregate functions, 148–150
Cartesian products, 133–137
creating, 132–133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138–140
natural joins, 145
outer joins, 145–148
performance considerations, 139
referential integrity, 131
self joins, 142–144
WHERE clause, 133–137
JSON PATH clause, 274
FROM keyword
retrieving individual columns, 31–33
retrieving multiple columns, 33–34
ORDER BY clause
example, 115–116
sorting by multiple columns, 43–44
sorting by single column, 41–43
OUTER JOIN clause, 146–147
overview, 31
PERCENT keyword, 38–39
retrieving variable values with, 216
sequence of clauses, 117
subqueries
as calculated fields, 123–126
checking for existance with, 126–128
correlated subqueries, 125–126
definition of, 119
filtering by, 119–123
TOP keyword, 37–39
UNION keyword, 154–157
variables in, 219–220
views
advantages of, 204–205
with calculated fields, 210–211
creating, 205
explained, 203–204
filtering data with, 209
reformatting retrieved data with, 207–208
removing, 205
reusable views, 207
rules and restrictions, 205
simplifying joins with, 206–207
updating, 205, 211–212
WHERE clause
AND operator, 57–58
comparison operators. See comparison operators
IN operator, 61–63
joins and, 133–137
nonmatches, checking for, 53
NOT operator, 63–64
NULL values, checking for, 54–55
OR operator, 58–59
order of evaluation, 59–61
range of values, checking for, 54
single values, checking against, 51–52
syntax, 49–50
wildcard filtering
brackets ([ ]) wildcard, 69–70
case sensitivity, 67
LIKE operator, 65–66
NULL values, 68
percent sign (%) wildcard, 66–68
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 68–69
selecting databases, 24–25
self joins, 142–144
semicolon (;), 33, 179–180
sequences
of clauses, 117
collation sequences, 278–280
of evaluation, 59–61, 80
ServerProperty() function, 279
SET IDENTITY_INSERT statement, 177
SET statement, 186, 215–216
setting up
access rights, 291–292
full-text searching
full-text catalogs, 163–166
full-text indexes, 164–166
full-text search support, 162–163
Sin() function, 94
sines, returning, 94
single quotes (’), 53, 169, 220
single values, checking against, 51–52
sorting combined query results, 158–159
sorting data
in ascending order, 46–47
case sensitivity and, 46
in descending order, 44–46
dictionary sort order, 46
grouping versus sorting, 114–116
by multiple columns, 43–44
by single column, 41–43
Soundex() function, 85–86
sp_columns stored procedure, 27–28
sp_databases stored procedure, 26
sp_fulltext_database stored procedure, 162–163
sp_helplogins stored procedure, 28, 289
SP_HELPTRIGGER stored procedure, 250
sp_helpuser stored procedure, 28
sp_rename stored procedure, 201
sp_server_info stored procedure, 28
sp_spaceused stored procedure, 28
sp_statistics stored procedure, 28
sp_tables stored procedure, 26–27
spaces
trailing spaces, 68
trimming, 76–77
SQL (Structured Query Language)
advantages of, 11
definition of, 11
SQL Server
advantages of, 13
client utilities, 15–16, 18
connecting to, 19–20
Database Engine Tuning Advisor, 296–297
debugger, 218
documentation, 297
installing, 18
JSON (JavaScript Object Notation) data
functions, 275
retrieving data as JSON, 273–274
obtaining, 17
server access, 16–17
server software, 14–15
SQL Server Express, 17
SQL Server Management Studio
locating, 18
navigating, 20–22
overview, 15–16
versions, 15
XML (Extensible Markup Language) data
retrieving, 264–268
searching for, 271–272
storing, 268–270
support for, 263
SQL Server Express, 17
SQL Server Management Studio
locating, 18
navigating, 20–22
overview, 15–16
Sqrt() function, 94
square brackets ([ ]), 275
Square() function, 94
square roots, returning, 94
squares, returning, 94
statements
ALTER FULLTEXT, 165–166
ALTER LOGIN, 290–291
ALTER TABLE, 199–200, 280, 291
ALTER TRIGGER, 249
autocommits, 259, 261
batches, 219
BEGIN, 223–224
BREAK, 226
case conventions, 33
CLOSE, 241–242
COMMIT, 259
CONTINUE, 226
CREATE FULLTEXT CATALOG, 163
CREATE FULLTEXT INDEX, 164
CREATE LOGIN, 290
CREATE PROCEDURE, 230–231
CREATE TABLE
collation sequences, 280
DEFAULT keyword, 197–198
default values, 197–198
example, 269
IDENTITY keyword, 195–197
NULL values, 193–194
primary keys, 194–195
syntax, 192–193
CREATE TRIGGER, 248
CREATE VIEW, 205
DEALLOCATE, 240–241
DECLARE, 214–215, 233, 240–241
DELETE
DELETE FROM, 188
DELETE triggers, 252
deleted data, reporting on, 188–189
guidelines, 188–189
syntax, 187–190
DISABLE TRIGGER, 250
DROP FULLTEXT, 165–166
DROP LOGIN, 290
DROP PROCEDURE, 231–232
DROP TABLE, 201
DROP TRIGGER, 249
DROP VIEW, 205
ENABLE TRIGGER, 250
END, 223–224
EXEC, 230
EXECUTE, 230
explicit commits, 259
FETCH, 242–245
formatting, 193
GRANT, 291–292
grouping, 223–224
IF, 221–223
INSERT
disabling, 175
INSERT SELECT, 180–183
INSERT triggers, 251–252
inserting complete rows, 176–179
inserting multiple rows, 179–180
inserting retrieved data, 180–183
INTO keyword, 179
omitting columns from, 179
OUTPUT clause, 183–184
overview, 175
returning list of changes, 183–184
Unicode text, 284–285
VALUES keyword, 177–179
OPEN, 241–242
PRINT, 217
REVOKE, 292–293
ROLLBACK, 258–259
rolling back, 258–259
SAVE TRANSACTION, 260–261
SELECT
aggregate functions, 97–107
ASC keyword, 46–47
AS keyword, 78
asterisk (*) wildcard, 35–36
batch processing, 218–219
clause ordering, 117
COLLATE keyword, 281–283
combined queries, 153–159
comparison operators, 49–50
concatenation (+) operator, 74–77
DESC keyword, 44–46
DISTINCT keyword, 36–37
EXISTS keyword, 126–128
FOR JSON AUTO clause, 273, 274
formatting, 122
FOR XML clause, 264–268
FROM clause, 133
FROM keyword, 31–34
FULL OUTER JOIN clause, 148
fully qualified table names, 39–40
GROUP BY clause, 110–111, 115–116
HAVING clause, 111–114
INNER JOIN clause, 137–138
INSERT SELECT, 180–183
joins. See joins
LIKE operator, 65–66
multiple columns, retrieving, 33–34
ORDER BY clause, 41–44, 115–116
OUTER JOIN clause, 146–147
overview, 31
PERCENT keyword, 38–39
retrieving variable values with, 216
TOP keyword, 37–39
UNION keyword, 154–157
variables in, 219–220
views, 203–212
WHERE clause. See WHERE clause
wildcard filtering, 65–71
SET, 186, 215–216
SET IDENTITY_INSERT, 177
subqueries
as calculated fields, 123–126
checking for existance with, 126–128
correlated subqueries, 125–126
definition of, 119
filtering by, 119–123
triggers
AFTER triggers, 249
creating, 248
DELETE triggers, 252
disabling, 249–250
dropping, 249
enabling, 249–250
explained, 247
guidelines, 253–254
INSERT triggers, 251–252
INSTEAD OF triggers, 249
multistatement triggers, 252
trigger assignments, determining, 250
UPDATE triggers, 253
updating, 249
UPDATE
guidelines, 188–189
syntax, 185–187
UPDATE triggers, 253
updated data, reporting on, 188–189
USE, 24–25
variables in, 218–220
WHILE, 225–226
white space in, 33
stored procedures
advantages of, 228–229
creating, 230–231
definition of, 25
disadvantages of, 229
dropping, 231–232
executing, 230
explained, 227–228
intelligent stored procedures, 235–238
parameters, 232–235
sp_columns, 27–28
sp_databases, 26
sp_fulltext_database, 162–163
sp_helplogins, 28, 289
SP_HELPTRIGGER, 250
sp_helpuser, 28
sp_rename, 201
sp_server_info, 28
sp_spaceused, 28
sp_statistics, 28
sp_tables, 26–27
storing XML (Extensible Markup Language) data, 268–270
Str() function, 85
strings
converting variables to, 217
empty strings, 194
text manipulation functions, 84–86
Structured Query Language. See SQL (Structured Query Language)
subqueries
as calculated fields, 123–126
checking for existance with, 126–128
correlated subqueries, 125–126
definition of, 119
EXISTS keyword, 126–128
filtering by, 119–123
SubString() function, 85
subtraction (-) operator, 80
Sum() function, 103–104
summarizing data
Avg() function, 98–99
Count() function
DISTINCT keyword, 106
explained, 100–101
explained, 97–98
Max() function
DISTINCT keyword, 106
explained, 101–102
Min() function, 102–103
Sum() function, 103–104
at symbol (@), 214, 233
SYS database, 289
system administrator (sa) login, 288
system administrators, 19
T
tables
aliases
explained, 141–142
naming, 107
columns
aliases, 77–78
average value of, returning, 98–99
deleting, 187–190
explained, 7–8
fully qualified column names, 133
identity columns, 28
maximum value, returning, 101–102
minimum value, returning, 102–103
omitting from INSERT operations, 179
returning list of, 27–28
sorting by, 41–44
totals, returning, 103–104
updating, 185–187
creating
basic table creation, 192–193
DEFAULT keyword, 197–198
default values, 197–198
explained, 191
IDENTITY keyword, 195–197
NULL values, 193–194
primary keys, 194–195
data. See data
datatypes, 7–8
definition of, 6–7
deleting, 201
different tables, combining, 159
dropping, 201
foreign keys, 11, 130
fully qualified table names, 39–40
joins
advantages of, 131
with aggregate functions, 148–150
Cartesian products, 133–137
creating, 132–133
cross joins, 136
definition of, 129
equijoins, 137
guidelines, 150
inner joins, 137
multiple table joins, 138–140
natural joins, 145
outer joins, 145–148
performance considerations, 139
referential integrity, 131
self joins, 142–144
simplifying with views, 206–207
WHERE clause, 133–137
primary keys
creating, 194–195
definition of, 9–11, 130
and NULL values, 195
relational tables, 129–131
renaming, 201
returning list of, 26–27
rows
counting, 100–101
deleting, 187–188
duplicate rows in unions, including/eliminating, 157–158
explained, 9–10
inserting, 176–183
updating, 185–187
sample tables, creating, 22–23
updating, 199–200
views
advantages of, 204–205
with calculated fields, 210–211
creating, 205
explained, 203–204
filtering data with, 209
reformatting retrieved data with, 207–208
removing, 205
reusable views, 207
rules and restrictions, 205
simplifying joins with, 206–207
updating, 205, 211–212
Tan() function, 94
tangents, returning, 94
testing calculations, 80
text
aggregate functions and
Max(), 102
Min(), 103
full-text searching
enabling, 162–163
explained, 161–162
full-text catalogs, 163–166
full-text indexes, 164–166
performance, 161
performing with CONTAINS, 168–172
performing with FREETEXT, 166–168
search results, ranking, 172–173
text manipulation functions, 84
time manipulation functions, 87–94
TOP keyword, 37–39
totals (column), returning, 103–104
trailing spaces, 68
transaction processing
autocommits, 259, 261
COMMIT statement, 259
explained, 255–257
ROLLBACK statement, 258–259
savepoints, 260–261
triggers
AFTER triggers, 249
creating, 248
DELETE triggers, 252
disabling, 249–250
dropping, 249
enabling, 249–250
explained, 247
guidelines, 253–254
INSERT triggers, 251–252
INSTEAD OF triggers, 249
multistatement triggers, 252
trigger assignments, determining, 250
UPDATE triggers, 253
updating, 249
trigonometric functions, 94
trimming spaces, 76–77
tuning performance, 295–297
U
underscore (_), 68–69
undoing statements, 258–259
Unicode, 283–285
UNION keyword, 154–157
UPDATE statement
guidelines, 188–189
syntax, 185–187
UPDATE triggers, 253
updated data, reporting on, 188–189
UPDATE triggers, 253
updated data, reporting on, 188–189
updating
data
guidelines, 188–189
syntax, 185–187
updated data, reporting on, 188–189
indexes, 165
tables, 199–200
triggers, 249
views, 205, 211–212
Upper() function, 84–85
uppercase, converting strings to, 85
USE statement, 24–25
user accounts
creating, 290
deleting, 290
enabling/disabling, 290
users
access rights, 291–293
logins, renaming, 291
managing, 289
user accounts
creating, 290
deleting, 290
enabling/disabling, 290
utilities, Database Engine Tuning Advisor, 296–297
V
value function, 271
values
assigning to variables, 215–216
NULL values
aggregate functions and. See aggregate functions
checking for, 54–55
compared to empty strings, 194
CREATE TABLE statement, 193–194
null value elimination warning, 150
primary keys and, 195
wildcards and, 68, 187
VALUES keyword, 177–179
variables
@ (at symbol), 214
assigning values to, 215–216
converting to strings, 217
declaring, 214–215
definition of, 214
rules and restrictions, 214
in statements, 218–220
viewing contents of, 216–217
versions of SQL Server, 15
viewing variable contents, 216–217
views
advantages of, 204–205
with calculated fields, 210–211
creating, 205
dropping, 205
explained, 203–204
filtering data with, 209
reformatting retrieved data with, 207–208
removing, 205
reusable views, 207
rules and restrictions, 205
simplifying joins with, 206–207
updating, 205, 211–212
W
warnings, null value elimination warning, 150
weight values (search), 173
WHERE clause
AND operator, 57–58
brackets ([ ]) wildcard, 69–70
comparison operators
BETWEEN operator, 54
equality operator (=), 51
IS NULL operator, 54–55
less than operator (<), 52
nonequality operator (<>), 53
nonequality operator (!=), 53
table of, 49–50
IN operator, 61–63
joins, 133–137
LIKE operator, 65–66
nonmatches, checking for, 53
NOT operator, 63–64
NULL values, checking for, 54–55
OR operator, 58–59
order of evaluation, 59–61
percent sign (%) wildcard, 66–68
range of values, checking for, 54
single values, checking against, 51–52
subqueries
as calculated fields, 123–126
checking for existance with, 126–128
correlated subqueries, 125–126
definition of, 119
EXISTS keyword, 126–128
filtering by, 119–123
syntax, 49–50
underscore (_) wildcard, 68–69
views, 209
WHILE statement, 225–226
white space, 33
wildcard filtering
* (asterisk), 61–62
brackets ([ ]) wildcard, 69–70
case sensitivity, 67
LIKE operator, 65–66
percent sign (%) wildcard, 66–68
tips for, 71
trailing spaces, 68
underscore (_) wildcard, 68–69
X-Y-Z
XML (Extensible Markup Language) data
retrieving, 264–268
searching for, 271–272
storing, 268–270
support for, 263
XML schemas, 271
XPath, 268, 272
XQuery, 269, 272
Year() function, 87, 92–93
Code Snippets
